package com.helmlabs.grouper.accounts.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.springframework.jdbc.core.simple.ParameterizedRowMapper;
import org.springframework.jdbc.core.support.JdbcDaoSupport;

import com.helmlabs.grouper.accounts.model.Address;
import com.helmlabs.grouper.accounts.model.Company;
import com.helmlabs.grouper.accounts.model.Role;

public class CompanyDaoJdbc extends JdbcDaoSupport implements CompanyDao {

	private RoleDao roleDao;
	
	public Company get(Long id) {
		String sql = "SELECT * FROM company c WHERE c.company_uid = ? LIMIT 1";
		Object[] args = {id};
		List<Company> companies = this.getJdbcTemplate().query(sql, args, new CompanyRowMapper());
		if (companies!=null && companies.size()>0){
			return companies.get(0);
		}
		return null;
	}

	public Company save(Company company) {
		return null;
	}
	
	private int getNumberOfAssociatedUsers(Long companyid){
		String sql = "SELECT count(*) FROM users_company uc WHERE uc.archive<>1 AND uc.company_fk=?";
		Object[] args = {companyid};
		return this.getJdbcTemplate().queryForInt(sql, args);
	}
	
	private class CompanyRowMapper implements ParameterizedRowMapper {
		public Object mapRow(ResultSet rs, int index) throws SQLException {
			Company company = new Company();
			company.setId(rs.getLong("company_uid"));
			company.setName(rs.getString("company_name"));
			company.setRoles(roleDao.getActiveRolesForObject(Role.COMPANY_ROLES, company.getId()));
			company.setNumberOfAssociatedUsers(getNumberOfAssociatedUsers(company.getId()));
			Address address = new Address();
			address.setStreet1(rs.getString("company_address1"));
			address.setStreet2(rs.getString("company_address2"));
			address.setCity(rs.getString("company_city"));
			address.setState(rs.getString("company_state"));
			address.setZipCode(rs.getString("company_zip"));
			company.setPrimaryAddress(address);
			return company;
		}
	}

	public void setRoleDao(RoleDao roleDao) {
		this.roleDao = roleDao;
	}

	public Company getCurrentCompanyByUserId(Long userid) {
		String sql = "SELECT * FROM company c, users_company uc WHERE uc.company_fk = c.company_uid AND uc.user_fk = ? AND uc.archive<>1 ORDER BY uc.date_joined DESC LIMIT 1";
		Object[] args = {userid};
		List<Company> companies = this.getJdbcTemplate().query(sql, args, new CompanyRowMapper());
		if (companies!=null && companies.size()>0)
			return companies.get(0);
		return null;
	}

	public List<Company> getPage(int page, int pageSize, String sort, String dir) {
		if (sort.equals("name"))
			sort = "company_name";
		
		String sql = "SELECT * FROM company c ORDER BY " + sort + " " + dir + " LIMIT " + (page*pageSize) + ", " + pageSize;
		return this.getJdbcTemplate().query(sql, new CompanyRowMapper());
	}

}
